﻿using Common.DataBase.Model;
using CommonUtils.Attributes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;

namespace Common.DataBase.Interface {

  public enum DataBaseType {
    SQLite,
    SQLServer,
    MySQL,
    Oracle,
    Unknown
  }


  public interface ISQLHelper : IDisposable {

    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    string ConnectString {
      get;
    }

    /// <summary>
    /// 数据库类型
    /// </summary>
    DataBaseType DBType {
      get;
    }

    /// <summary>
    /// 查询所有用户表的字符串
    /// </summary>
    string QueryUserTableSQL {
      get;
    }

    /// <summary>
    /// 根据指定表名查询列信息的SQL
    /// </summary>
    string QueryColumnsByTableNameSQL {
      get;
    }

    /// <summary>
    /// 数据类型映射关系
    /// </summary>
    Dictionary<string,string> DataTypeMap {
      get;
    }

    /// <summary>
    /// 构造SQL参数
    /// </summary>
    /// <param name="parameterName">参数名</param>
    /// <param name="value">参数值</param>
    /// <returns>参数对象</returns>
    DbParameter DbParam(string parameterName, object value);

    /// <summary>
    /// 打开数据库连接
    /// </summary>
    /// <returns></returns>
    DbConnection OpenConnection();
    
    /// <summary>
    /// 打开数据库连接
    /// </summary>
    /// <returns></returns>
    void CloseConnection();

    /// <summary>
    /// 执行不带参数的增删改SQL语句或存储过程
    /// </summary>
    /// <param name="commandText">要执行的SQL语句或存储 过程</param>
    /// <returns>返回受影响的记录数</returns>
    int ExecuteNonQuery(string commandText, bool isClose = true);

    /// <summary>
    /// 执行不带参数的增删改SQL语句或存储过程
    /// </summary>
    /// <param name="commandText">要执行的SQL语句或存储 过程</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回受影响的记录数</returns>
    int ExecuteNonQuery(string commandText, CommandType commandType , bool isClose = true);

    /// <summary>
    /// 执行带参数的增删改SQL语句或存储过程
    /// </summary>
    /// <param name="commandText">要执行的SQL语句或存储 过程</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name ="parameters">参数数组</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回受影响的记录数</returns>
    int ExecuteNonQuery(string commandText, CommandType commandType, DbParameter[] parameters , bool isClose = true);


    /// <summary>
    /// 执行传入的不带参数的SQL查询语句或存储过程
    /// </summary>
    /// <param name="commandText">SQL查询语句或存储过程</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    DataTable ExecuteQuery(string commandText, bool isClose = true);

    /// <summary>
    /// 执行传入的不带参数的SQL查询语句或存储过程
    /// </summary>
    /// <param name="commandText">SQL查询语句或存储过程</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    DataTable ExecuteQuery(string commandText, CommandType commandType, bool isClose = true);

    /// <summary>
    /// 该方法执行带参数的SQL查询语句
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    DataTable ExecuteQuery(string commandText, CommandType commandType, DbParameter[] parameters, bool isClose = true);


    /// <summary>
    /// 查询对象集合
    /// 执行传入的不带参数的SQL查询语句或存储过程
    /// </summary>
    /// <typeparam name="T">返回的集合中的数据类型</typeparam>
    /// <param name="commandText">SQL查询语句或存储过程</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    List<T> FindList<T>(string commandText, bool isClose = true) where T : new();


    /// <summary>
    /// 查询对象集合
    /// 执行传入的不带参数的SQL查询语句或存储过程
    /// </summary>
    /// <typeparam name="T">返回的集合中的数据类型</typeparam>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    List<T> FindList<T>(string commandText, CommandType commandType, bool isClose = true) where T : new();


    /// <summary>
    /// 查询对象集合
    /// 该方法执行带参数的SQL查询语句
    /// </summary>
    /// <typeparam name="T">返回的集合中的数据类型</typeparam>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns></returns>
    List<T> FindList<T>(string commandText, CommandType commandType, DbParameter[] parameters, bool isClose = true) where T : new();


    /// <summary>
    /// 从数据库中检索单个值
    /// </summary>
    /// <param name="commandText">SQL查询语句或存储过程</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回值是object类型</returns>
    object ExecuteScalar(string commandText, bool isClose = true);

    /// <summary>
    /// 从数据库中检索单个值
    /// </summary>
    /// <param name="commandText">SQL查询语句或存储过程</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回值是object类型</returns>
    object ExecuteScalar(string commandText, DbParameter[] parameters, bool isClose = true);

    /// <summary>
    /// 从数据库中检索单个值
    /// </summary>
    /// <param name="commandText">要执行的SQL语句</param>
    /// <param name ="commandType">命令类型</param>
    /// <param name="parameters">参数集合</param>
    /// <param name="isClose">执行完是否关闭连接</param>
    /// <returns>返回值是object类型</returns>
    object ExecuteScalar(string commandText, DbParameter[] parameters,CommandType commandType , bool isClose = true);


    /// <summary>
    /// 生成Insert语句
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    string GenerateInsertSQL(Type type);


    /// <summary>
    /// 生成Update语句
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    string GenerateUpdateSQL(Type type);


    /// <summary>
    /// 生成Delete语句
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    string GenerateDeleteSQL(Type type);


    /// <summary>
    /// 生成根据主键查询一条记录的语句
    /// </summary>
    /// <param name="type"></param>
    /// <returns></returns>
    string GenerateSelectSQLByPrimaryKey(Type type);

    /// <summary>
    /// 将对象的所有属性生成参数
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t">实体对象</param>
    /// <returns></returns>
    DbParameter[] ConstructEntityParameters<T>(T t);
        
    /// <summary>
    /// 生成主键参数
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t">实体对象</param>
    /// <returns></returns>
    DbParameter[] ConstructPrimaryKeyParameter<T>(T t);


    /// <summary>
    /// 获取所有用户表信息
    /// </summary>
    /// <returns></returns>
    List<Model.TableInfo> GetAllUserTables();


  }

  public static class ISQLHelperExtendMethods {

    public static string GenerateInsertSQL2<T>(this T t,Type type) where T : ISQLHelper {
      string insertSQL = string.Empty;
      //调用GetCustomAttributes方法获取与DBTable类有关的属性列表，该方法返回一个对象数组
      Object[] tableAttributes = type.GetCustomAttributes(typeof(DBTable), false);
      if(tableAttributes.GetLength(0) != 0) {
        DBTable dbTable = (DBTable)tableAttributes[0];//获取属性类的第一个成员
        string tableName = dbTable.TableName;
        if(!string.IsNullOrWhiteSpace(tableName)) {
          insertSQL = string.Format("INSERT INTO {0}(", tableName);
          string values = string.Format(" VALUES(@");
          PropertyInfo[] propertys = type.GetProperties();
          int fieldCount = propertys.Length;
          for(int i = 0; i < fieldCount; i++) {
            PropertyInfo info = propertys[i];
            Attribute[] dbColumnAttributes = Attribute.GetCustomAttributes(info);
            foreach(Attribute dbColumnAttr in dbColumnAttributes) {
              if(dbColumnAttr.GetType() == typeof(DBColumn)) {
                string fieldName = ((DBColumn)dbColumnAttr).ColumnName;
                insertSQL += (i < fieldCount - 1) ? fieldName + "," : fieldName + ")";
                values += (i < fieldCount - 1) ? info.Name + ",@" : info.Name + ");";
              }
            }
          }
          insertSQL += values;
        }
      }
      return insertSQL;
    }


    public static void GenerateModel<T>(this T t) where T : ISQLHelper {
      //DataTable dataTable = t.ExecuteQuery(t.QueryUserTableSQL, false);
      //List<TableInfo> tableList = new List<TableInfo>();
      //foreach (DataRow dataRow in dataTable.Rows) {
      //  TableInfo table = new TableInfo();
      //  DataColumn column = dataTable.Columns[0];
      //  object tableName = dataRow[column.ColumnName];
      //  if (tableName != DBNull.Value) {
      //    table.TableName = tableName.ToString();
      //    DataTable dataColumnsTable = t.ExecuteQuery(t.QueryColumnsByTableNameSQL, false);
      //    foreach (DataRow columnRow in dataColumnsTable.Rows) {
      //      FieldInfo table = new FieldInfo();
      //      DataColumn column = dataTable.Columns[0];
      //      object tableName = dataRow[column.ColumnName];
      //      if (tableName != DBNull.Value) {
      //        table.TableName = tableName.ToString();
      //        DataTable dataColumnsTable = t.ExecuteQuery(t.QueryColumnsByTableNameSQL, false);



      //      }
      //    }


      //  }
      //}




        //  foreach (PropertyInfo property in properties) {
        //    foreach (DataColumn column in dataTable.Columns) {
        //      if (column.ColumnName.ToLower().Trim() == property.Name.ToLower().Trim()) {
        //        object value = dataRow[column.ColumnName];
        //        if (value != DBNull.Value && property.CanWrite) {
        //          property.SetValue(model, value, null);
        //        }
        //      }
        //    }


        //  }
        //  list.Add(model);
        //}



    }
   

   
  }

}
